/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package attendance;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 *
 * @author Abdiweli
 */
public class Attendance {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		
		String dbur1 = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=\\\\KAOQIN\\\\iCCard\\\\iCCard.mdb";
		Connection conn = DriverManager.getConnection(dbur1, "admin", "168168");
		Statement stmt = conn.createStatement();
		
		String firstDay = getBeginDate();
		String lastDay = getEndDate();
		
		// AND f_ControllerSN = '62137'
		String SQL = "SELECT * FROM t_d_CardRecord WHERE f_ConsumerID = 6832 AND f_ControllerSN = '62137' AND f_ReadDate between #" + firstDay + "# AND #" + lastDay + "# ORDER BY f_ReadDate desc";
		System.out.println(SQL);
		
		ResultSet rs = stmt.executeQuery(SQL);
		ResultSetMetaData metaData = null;

		while (rs.next()) {
			if (metaData == null)
				metaData = rs.getMetaData();

			for (int i = 1, len = metaData.getColumnCount(); i <= len; i++) {
				String value = rs.getString(i);
				System.out.print(value + "\t");
				
				if(i == 3) {
					String[] date = value.split(" ")[1].split("\\:");
					
					int hour = Integer.parseInt(date[0]);
					int minute = Integer.parseInt(date[1]);
					
					if(hour == 10 && minute >= 5) {
						System.out.print("迟到\t");
					}else {
						System.out.print("-\t");
					}
				}
				
				if (i == len) {
					System.out.println("\t");
				} 
			}
		}
		
		rs.close();
		
//		SQL = "UPDATE t_d_CardRecord SET f_ReadDate = #2016-03-10 10:04:14# WHERE f_CardRecordID = 452827";
//		System.out.println();
//		System.out.println(SQL);
//		System.out.println("Update成功：" + stmt.executeUpdate(SQL));
		
		stmt.close();
		conn.close();
    }
    
    private static String getBeginDate() {
		Calendar begin = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

		begin.set(Calendar.DAY_OF_MONTH, begin.getActualMinimum(Calendar.DAY_OF_MONTH));
		begin.set(Calendar.SECOND, 0);
		begin.set(Calendar.HOUR_OF_DAY, 0);
		begin.set(Calendar.MINUTE, 0);

		return sdf.format(begin.getTime());
	}

	private static String getEndDate() {
		Calendar end = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

		end.set(Calendar.DAY_OF_MONTH, end.getActualMaximum(Calendar.DAY_OF_MONTH));
		end.set(Calendar.SECOND, 59);
		end.set(Calendar.HOUR_OF_DAY, 23);
		end.set(Calendar.MINUTE, 59);

		return sdf.format(end.getTime());
	}
}
